package edu.example;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.TreeMap;
import java.util.Vector;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;


import edu.example.dto.HibernateApplication;
import edu.example.dto.HibernateOTS;

public class ApplicationManagerCloud
{
	private static ApplicationManagerCloud instance = null;
	
	private static UploadAppDialog mainGUI;
	
	private static File ayDataFile;
	private static File ayMapFile;
	
	private static File otsDataFile;
	private static File otsMapFile;
	
	private boolean mapCheckFlag = false;
	
	//Useful data Headers
	private Vector<String> ayHeader =  new Vector<String>();
	
	//Useful data Headers
	private Vector<String> otsHeader =  new Vector<String>();
	
	//AY file Headers
	private Vector<String> ayDataHeader =  new Vector<String>();
	
	//OTS file Headers
	private Vector<String> otsDataHeader =  new Vector<String>();
	
	private Vector<String> dbAyDataHeader = new Vector<String>();
	
	private Vector<String> dbOTSDataHeader = new Vector<String>();
	
	
	private Vector<String> dbreader = new Vector<String>();
	
	private Application currentApplication= new Application();
	
	//Newly uploaded Applications
	private TreeMap<String, Application> applications = new TreeMap<String, Application>();
	
	//Applications that already exist in DB
	private TreeMap<String, Application> dbApplications = new TreeMap<String, Application>();
	
	//All applications including newly uploaded and in the DB
	private TreeMap<String, Application> allApplications = new TreeMap<String, Application>();
	
	//OTSs that already exist in DB
	private TreeMap<String, OfficialTestScore> dbOTS = new TreeMap<String, OfficialTestScore>();
	
	//All OTSs including newly uploaded and in the DB
	private TreeMap<String, OfficialTestScore> allOTS = new TreeMap<String, OfficialTestScore>();
	
	
	public Vector<String> 						getAYHeader()		{return ayDataHeader;}
	public boolean 								getFlag()			{return mapCheckFlag;}
	public TreeMap<String, Application> 		getAllApps()		{return allApplications;}
	public TreeMap<String, Application> 		getNewApps()		{return applications;}
	public TreeMap<String, OfficialTestScore>	getAllScore()		{return allOTS;}
	

	public static ApplicationManagerCloud 			getAppManager()
	{
		 if (instance == null) 
			 instance = new ApplicationManagerCloud(mainGUI, ayDataFile,ayMapFile,otsDataFile,otsMapFile);
		 return instance;
	 }
	

	

	public ApplicationManagerCloud(UploadAppDialog intiGUI, 
								File initAYDataFile, 
								File initAYMapFile,
								File initOTSFile,
								File initOTSMapFile)
	{
		mainGUI = intiGUI;
		ayDataFile = initAYDataFile;
		ayMapFile = initAYMapFile;
		otsDataFile = initOTSFile;
		otsMapFile = initOTSMapFile;
		if(dbApplications.isEmpty())
		{
			ayDummyDatabaseRead();
			loadSettingData();
			//System.out.println("read ay db");
		}
		if(dbOTS.isEmpty())
		{
			OTSDummyDatabaseRead();
			//System.out.println("read ots db");
		}
		if(ayDataFile != null)
		{
			ayDataProcessing(ayDataFile,ayMapFile);
		}
		if(otsDataFile != null)
		{
			otsDataProcessing(otsDataFile,otsMapFile);
		}
	}
	
	
	//Overwrite the Method
	public void ayDummyDatabaseRead()
	{				
		File file = new File("AY-map.txt");
		int counter = 0;		
		try
		{
			FileInputStream fis = new FileInputStream(file);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				int endIndex = strLine.indexOf("->");
				if(endIndex > 0)
				{
					String mapTo = strLine.substring(endIndex+2);										
					dbAyDataHeader.add(counter, mapTo);
				}
				counter++;
			}
			din.close();
		}
		catch (Exception e)
		{			
			System.err.println("Error: " + e.getMessage());	
		}
		String reader = null;
		dbreader= new Vector<String>();
		TreeMap<String, String> CurrentData = new TreeMap<String, String>();
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
                        
            PreparedStatement s=conn.prepareStatement("select * from aydata",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		CurrentData = new TreeMap<String, String>();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<81;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);    
            				
            	    		}
            		for(int i = 0; i < dbAyDataHeader.size(); i++)
					{
						CurrentData.put(dbAyDataHeader.get(i), dbreader.get(i));
						//System.out.println(ayDataHeader.get(i)+": "+dbreader.get(i));
					}
            		
            		Application newApp = new Application(CurrentData);
					newApp.setNumDesiredReviews(3);
					
					if(allApplications.containsKey(newApp.getEmailAddress()))
					{
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					else
					{
						//applications.put(newApp.getEmailAddress(), newApp);
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					System.out.println("read 1 ay from db");
            	}
            }
            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		e.printStackTrace();
    	}		 
	}
	
	//Over Write The Method
	public void OTSDummyDatabaseRead()
	{
		File file = new File("OTS-map.txt");
		int counter = 0;		
		try
		{
			FileInputStream fis = new FileInputStream(file);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				int endIndex = strLine.indexOf("->");
				if(endIndex > 0)
				{
					String mapTo = strLine.substring(endIndex+2);										
					dbOTSDataHeader.add(counter, mapTo);
				}
				counter++;
			}
			din.close();
		}
		catch (Exception e)
		{			
			System.err.println("Error: " + e.getMessage());	
		}
		String reader = null;
		dbreader= new Vector<String>();
		TreeMap<String, String> CurrentData = new TreeMap<String, String>();
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
                        
            PreparedStatement s=conn.prepareStatement("select * from otsdata",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		CurrentData = new TreeMap<String, String>();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<23;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);                    
            	    		}            		
            		for(int i = 0; i < dbOTSDataHeader.size(); i++)
					{
						CurrentData.put(dbOTSDataHeader.get(i), dbreader.get(i));
						//System.out.println(dbOTSDataHeader.get(i)+": "+dbreader.get(i));
						
					}
					OfficialTestScore newScore = new OfficialTestScore(CurrentData);
					
					if(allOTS.containsKey(newScore.getEmailAddress()))
					{
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					else
					{
						//OTS.put(newScore.getEmailAddress(), newScore);
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					System.out.println("read 1 ots from db");
            	}
            }
            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		//e.printStackTrace();
    	}
	}
	
	private TreeMap<String, String> ayMap = new TreeMap<String, String>();
	private Vector<String> ayIgnoredField = new Vector<String>();
	
	private Vector<String> readAYheader(File mapFile)
	{
		try 
		{
			FileInputStream fis = new FileInputStream(mapFile);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			
			String line;
			try 
			{
				while((line = br.readLine()) != null)
				{
					String[] parts = line.split("->");
					if(parts[1].equals("ignore"))
					{
						ayIgnoredField.add(parts[0]);
					}
					else if((parts[1] != null))
					{
						ayMap.put(parts[0], parts[1]);
					}
				}
			} 
			catch (IOException e) 
			{
				e.printStackTrace();
			}
		} 
		catch (FileNotFoundException e) 
		{
			e.printStackTrace();
		}
		return ayHeader;
	}
	
	public void ayDataProcessing(File dataFile, File mapFile)
	{
		boolean error = false;
		Vector<HibernateApplication> uploadedApplication = new Vector<HibernateApplication>();
		int lineCounter = 0;
		String word[] = null;
		try
		{
			FileInputStream dataFis = new FileInputStream(dataFile);
			DataInputStream dataDin = new DataInputStream(dataFis);
			BufferedReader dataBr = new BufferedReader(new InputStreamReader(dataDin));
			
			this.readAYheader(mapFile);
			
			String strLine;
			while ((strLine = dataBr.readLine()) != null)   
			{	
				HibernateApplication app = new HibernateApplication();
				strLine = strLine.replaceAll("\"", "");
				
				for(int i = 0; i < strLine.length(); i++)
				{
					word = strLine.split("	");
				}
				if (lineCounter == 0)
				{
					for(int i = 0; i < word.length; i++)
					{
						ayDataHeader.add(i, word[i]);
						if(!ayIgnoredField.contains(word[i]) && !ayMap.containsKey(word[i]))
						{
							System.out.println("Error in field No."+(i+1));
							error = true;
						}
					}
				}
				else
				{
					for(int i = 0; i < word.length; i++)
					{
						String header = ayMap.get(ayDataHeader.get(i));
						if(header == null)
						{
							System.out.println("Error");
						}
						else
						{
							System.out.println(header);
							Class<?> c = app.getClass();
							Field f = c.getDeclaredField(header);
							f.setAccessible(true);
							f.set(app, word[i]);
						}
					}
					uploadedApplication.add(app);
				}
				lineCounter++;
			}
			dataDin.close();
						 
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		if(error)
			return;
		
		Configuration config = new Configuration().configure();
		ServiceRegistry serviceReg = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();
		SessionFactory sessionF = new Configuration().configure().buildSessionFactory(serviceReg);
		Session session = sessionF.openSession();
		session.beginTransaction();
		for(HibernateApplication a: uploadedApplication)
		{
			System.out.println(a.getFirst_name());
			session.saveOrUpdate(a);
		}
		session.getTransaction().commit();
		session.close();
	}
	
	private TreeMap<String, String> otsMap = new TreeMap<String, String>();
	
	private Vector<String> readOTSheader(File mapFile)
	{
		try 
		{
			FileInputStream fis = new FileInputStream(mapFile);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			
			String line;
			try 
			{
				while((line = br.readLine()) != null)
				{
					String[] parts = line.split("->");
					if((!parts[1].equals("ignore"))&&(parts[1] != null))
					{
						otsMap.put(parts[0], parts[1]);
					}
				}
			} 
			catch (IOException e) 
			{
				e.printStackTrace();
			}
		} 
		catch (FileNotFoundException e) 
		{
			e.printStackTrace();
		}
		return otsHeader;
	}
	
	public void otsDataProcessing(File dataFile, File mapFile)
	{
		Vector<HibernateOTS> uploadedOTS = new Vector<HibernateOTS>();
		int lineCounter = 0;
		String word[] = null;
		try
		{
			FileInputStream dataFis = new FileInputStream(dataFile);
			DataInputStream dataDin = new DataInputStream(dataFis);
			BufferedReader dataBr = new BufferedReader(new InputStreamReader(dataDin));
			
			this.readOTSheader(mapFile);
			
			String strLine;
			while ((strLine = dataBr.readLine()) != null)   
			{	
				HibernateOTS ots = new HibernateOTS();
				strLine = strLine.replaceAll("\"", "");
				
				for(int i = 0; i < strLine.length(); i++)
				{
					word = strLine.split("	");
				}
				if (lineCounter == 0)
				{
					for(int i = 0; i < word.length; i++)
					{
						otsDataHeader.add(i, word[i]);
					}
				}
				else
				{
					for(int i = 0; i < word.length; i++)
					{
						String header = otsMap.get(otsDataHeader.get(i));
						if(header ==  null)
						{
						}
						else
						{
							Class<?> c = ots.getClass();
							Field f = c.getDeclaredField(header);
							f.setAccessible(true);
							f.set(ots, word[i]);
						}
					}
					uploadedOTS.add(ots);
				}
				lineCounter++;
			}
			dataDin.close();
						 
		}
		catch (Exception e)
		{
			System.err.println("Error: " + e.getMessage());
		}
		
		Configuration config = new Configuration().configure();
		ServiceRegistry serviceReg = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();
		SessionFactory sessionF = new Configuration().configure().buildSessionFactory(serviceReg);
		Session session = sessionF.openSession();
		session.beginTransaction();
		for(HibernateOTS o: uploadedOTS)
		{
			System.out.println(o.getEmail_address());
			String sql = "from HibernateApplication A where A.email_address ="+"'"+o.getEmail_address()+"'";
			Query q = session.createQuery(sql);
			List<HibernateOTS> list = (List<HibernateOTS>)q.list();
			if(list.size() == 1)
			{
				session.saveOrUpdate(o);
			}	
			else
			{
				System.out.println("GARS: Application Record not found");
			}
			
		}
		session.getTransaction().commit();
		session.close();
	}
	
	public void loadSettingData(){
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
            
            PreparedStatement s=conn.prepareStatement("select * from appinfo",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    String reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		currentApplication= new Application();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<10;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);    
            				
            	    		}
            		Collection<Application> temp = allApplications.values();
	    			Iterator<Application> it = temp.iterator();
	    				
	    			while(it.hasNext())
	    			{
	    				Application tempApp = it.next();
	    				if(tempApp.getEmailAddress().equals(dbreader.elementAt(0))){
	            			currentApplication=tempApp;
	            			currentApplication.setTier(dbreader.elementAt(1));
	            			currentApplication.setSummary(dbreader.elementAt(1));
	            			currentApplication.setTOEFLComments(dbreader.elementAt(1));
	            			currentApplication.setNumDesiredReviews(Integer.parseInt(dbreader.elementAt(1)));
	            			currentApplication.setSBUID(dbreader.elementAt(1));
	            			currentApplication.setSBUGPA(dbreader.elementAt(1));
	            			currentApplication.setRelatedFile(dbreader.elementAt(1));
	            			currentApplication.setStatus(dbreader.elementAt(1));
	    				}
	    			}
            		
					
					System.out.println("read 1 appinfo from db");
            	}
            }            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		e.printStackTrace();
    	}		
	}
	
	public void updateSettingData(){
		if(!allApplications.isEmpty())
		{											
				
				try{
				    Connection conn=ConnectionUtils.getConnection();
				    Statement statement = conn.createStatement();
				    String sql = "delete from appinfo";
				    int delete = statement.executeUpdate(sql);
				    
				    
				    conn.setAutoCommit(false);
				    String checker ="";
				    
				    Collection<Application> temp = allApplications.values();
	    			Iterator<Application> it = temp.iterator();
	    				
	    			while(it.hasNext())
	    			{
	    				Application tempApp = it.next();
	    				PreparedStatement stmt = conn.prepareStatement("INSERT INTO appinfo VALUES ( ?,?,?,?,?  ,?,?,?,?)");
				        
	    				
				        stmt.setString(1,  tempApp.getEmailAddress());
				        stmt.setString(2,  tempApp.getTier());
				        stmt.setString(3,  tempApp.getSummary());
				        stmt.setString(4,  tempApp.getTOEFLComments());
				        stmt.setString(5,  tempApp.getNumDesiredReviews());
				        stmt.setString(6,  tempApp.getSBUID());
				        stmt.setString(7,  tempApp.getSBUGPA());
				        stmt.setString(8,  tempApp.getRelatedFile());
				        stmt.setString(9,  tempApp.getStatus());
	    				
				        checker=tempApp.getEmailAddress();
					    if(checker==null)
					    	stmt.setString(1,  "");
					    checker=tempApp.getTier();
					    if(checker==null)
					    	stmt.setString(2,  "");
					    checker=tempApp.getSummary();
					    if(checker==null)
					    	stmt.setString(3,  "");
					    checker=tempApp.getTOEFLComments();
					    if(checker==null)
					    	stmt.setString(4,  "");
					    checker=tempApp.getNumDesiredReviews();
					    if(checker==null)
					    	stmt.setString(5,  "");
					    checker=tempApp.getSBUID();
					    if(checker==null)
					    	stmt.setString(6,  "");
					    checker=tempApp.getSBUGPA();
					    if(checker==null)
					    	stmt.setString(7,  "");
					    checker=tempApp.getRelatedFile();
					    if(checker==null)
					    	stmt.setString(8,  "");
					    checker=tempApp.getStatus();
					    if(checker==null)
					    	stmt.setString(9,  "");
				        stmt.addBatch();
					    // submit the batch for execution
				        int[] updateCounts = stmt.executeBatch();
				        conn.commit(); 
				        System.out.println("app setting is updated in db");
	    			}
			    
		        
		        conn.close();
				}catch(Exception e){
		    		e.printStackTrace();
		    	}	
				
			
			
		}
	}
	public String toString()
	{
		String s = "";
		Collection<Application> temp = allApplications.values();
		Iterator<Application> it = temp.iterator();
			
		while(it.hasNext())
		{
			Application tempApp = it.next();
			Collection<String> tempV = tempApp.getData().values();
			Collection<String> tempK = tempApp.getData().keySet();
			Iterator<String> itK = tempK.iterator();
			Iterator<String> itV = tempV.iterator();
				
			while(itK.hasNext())
			{
				String tempKey = itK.next();
				String tempValue = itV.next();
				
				//Here is the key and value, WRITE IT BACK TO DATABASE
				s = s+(tempKey+": "+tempValue+"\n");
				//System.out.println(tempKey+": "+tempValue);
			}
			//System.out.println(" ");
		}
		
		
		Collection<OfficialTestScore> tempS = allOTS.values();
		Iterator<OfficialTestScore> itS = tempS.iterator();
			
		while(itS.hasNext())
		{
			OfficialTestScore tempScore = itS.next();
			Collection<String> tempV = tempScore.getData().values();
			Collection<String> tempK = tempScore.getData().keySet();
			Iterator<String> itK = tempK.iterator();
			Iterator<String> itV = tempV.iterator();
			
			while(itK.hasNext())
			{
				String tempKey = itK.next();
				String tempValue = itV.next();
				
				s = s+(tempKey+": "+tempValue+"\n");
				//Here is the key and value, WRITE IT BACK TO DATABASE
				//System.out.println(tempKey+": "+tempValue);
			}
			//System.out.println(" ");
		}
		return s;
	}
	
	public static void main(String[] args)
	{
		File file1 = new File("AY(new).txt");
		File file2 = new File("AY-map.txt");
		File file3 = new File("OTS.txt");
		File file4 = new File("OTS-map.txt");
		ApplicationManagerCloud formatter = new ApplicationManagerCloud(null, file1, file2, file3, file4);
		//System.out.println(formatter.toString());
		//System.out.println(formatter.getApps().get("maurice.wilkes@bmail.com").getApplicantLastName());
	}
}